﻿<cfsilent>
	<cfflush interval="1024" />
	<cfscript>
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		
		teacherId = sessionAdvice.getAutherUserID();
		
		hashCode = event.getArg("Sign");
		taskId = event.getArg("TSK");
		taskType = event.getArg("TT");
		
		checked = false;
		
		try {
			
			if ( decrypt(hashCode, teacherId, "PBEWithMD5AndDES", "Hex") eq taskId ) {
				
				checked = true;
				
				/* 载入补考教学任务 */
				sql="SELECT b.mark_mode 
					 FROM t_resitplan a 
     					  INNER JOIN t_course b ON a.cid=b.cid 
					 WHERE a.task_id=:taskId ";
					 
				queryObj = new Query( datasource=application.dnsSlave ); 
				
				queryObj.addParam( name="taskId", value=taskId, cfsqltype="cf_sql_varchar" );
				
				rs_task = queryObj.execute( sql=sql ).getResult();
				
				/* 读取成绩提交模式 */
				markMode = rs_task.mark_mode;
				
				/* 成绩提交默认为 百分制 */
				markSubmitEvent = "taskMarkResitWizardMode0";
		
				/* 设置为两级制 */
				if ( markMode eq "2" ) {
					markSubmitEvent = "taskMarkResitWizardMode2";
				}

				/* 设置为四级制 */
				if ( markMode eq "4" ) {
					markSubmitEvent = "taskMarkResitWizardMode4";
				}
				
				
				/* 导入成绩教师 */
				sql="SELECT a.tch_id,a.tch_name,a.tch_pyidx,
						    a.tch_sex,a.tch_idcard,a.tch_email,
						    a.tch_tel,a.tch_create,a.tch_lastupdate,
						    a.institute_id,a.tp_id,a.edu_id,a.tch_type 
					 FROM t_teacher a 
					 WHERE a.tch_id = :teacherId ";
					 
				queryObj = new Query( datasource=application.dnsSlave ); 
				
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				
				rs_teacher = queryObj.execute( sql=sql ).getResult();

				temFile = GetTempDirectory() & createUUID() & ".xls";

				markUpdate = 0;
		
				targetArgs = structNew();
				structInsert(targetArgs, "TabID", "labSheet", true);
				structInsert( targetArgs, "TSK", taskId, true );
				structInsert( targetArgs, "TT", taskType, true );
				structInsert( targetArgs, "Sign", encrypt(taskId, teacherId, "PBEWithMD5AndDES", "Hex"), true );
				
			}
			
		}
		catch (any ecpt) {
		}
		
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入成绩数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>
	
	<cfif isDefined("FORM.upload") and checked >
	
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="成绩名单">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入成绩信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>
				
				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "补考成绩")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,考核成绩</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p>

					<cftransaction>

						<cfloop query="rs">
							
							<cfset mark = rs["补考成绩"][rs.currentRow] />
							<cfset markNote = "1" />
							
							<cfset scroll = "" />
                            <cfscript>
                            	sql="SELECT 
											a.stu_name 
										FROM 
											t_student a 
										WHERE 
											a.stu_id = :studentId ";
								
							    queryObj = new Query( datasource=application.dnsMaster ) ;
								
                                queryObj.addParam( name="studentId", value=rs["学号"][rs.currentRow], cfsqltype="cf_sql_varchar" );
								
                                rs_student = queryObj.execute( sql=sql ).getResult();
                            </cfscript>

							<cfif rs_student.recordCount GT 0 and rs["姓名"][rs.currentRow] eq rs_student.stu_name >
								
									<!--- 学号姓名有匹配记录, 开始转置成绩信息 --->
							
									<!--- /* 根据成绩备注矫正分数 */ --->
									
									<!--- 若 缺考 --->
									<cfif mark eq "缺考">
										<cfset scroll = 0 />
										<cfset markNote = "2" />
									</cfif>
									<!--- 若 违纪 --->
									<cfif mark eq "违纪">
										<cfset scroll = 0 />
										<cfset markNote = "9" />
									</cfif>

									<!--- 若 0-100 数字 --->
									
									<!--- /* 根据成绩计分模式调整分数 */ --->
									<cfswitch expression="#markMode#">
										
										<cfcase value="0">
											<!--- /* 百分制最多保留2位小数 */ --->
											<cfif isNumeric( mark ) and mark gte 0 and mark lte 100 >
												<cfset scroll = round( mark * 10 ) / 10 />
											</cfif>
										</cfcase>
										
										<cfcase value="2">
											<!--- /* 二级制则转换 通过 未通过 */ --->
											<cfif mark eq "通过"><cfset scroll = 90 /></cfif>
											<cfif mark eq "未通过"><cfset scroll = 0 /></cfif>
										</cfcase>
										
										<cfcase value="4">
											<!--- /* 四级制则转换 优秀 良好 合格 不合格 */ --->
											<cfif mark eq "优秀"><cfset scroll = 90 /></cfif>
											<cfif mark eq "良好"><cfset scroll = 80 /></cfif>
											<cfif mark eq "合格"><cfset scroll = 70 /></cfif>
											<cfif mark eq "不合格"><cfset scroll = 0 /></cfif>
										</cfcase>
										
									</cfswitch>
									
									<cfif isNumeric( scroll )>
											<cfscript>
												 sql = "SELECT 
																a.mark_limite 
															FROM 
																t_temp_mark_resit a 
																INNER JOIN t_resitplan b ON a.task_id = b.task_id 
																INNER JOIN t_student c ON a.stu_id = c.stu_id 
															WHERE 
																b.task_id = :taskId 
																AND 
																c.stu_id = :studentId "; 
																
												queryObj = new query( datasource=application.dnsMaster ); 
												
												queryObj.addParam( name="taskId", value=taskId, cfsqltype="cf_sql_varchar" );
												queryObj.addParam( name="studentId", value=rs["学号"][rs.currentRow], cfsqltype="cf_sql_varchar" );
												
												rs_studentMark = queryObj.execute( sql=sql ).getResult();
												
											</cfscript>
											<cfif rs_studentMark.recordCount GT 0 >
													
													<!--- 处理成绩上限 --->
													<cfif scroll gt rs_studentMark.mark_limite>
														<cfset scroll = rs_studentMark.mark_limite />
													</cfif>
                                                	<cftransaction>
                                                    	<cfset  sql = "UPDATE 
																			(SELECT 
																				mark_mode,mark_renote,mark_resit
																			FROM 
																				t_temp_mark_resit a 
																				INNER JOIN t_resitplan b ON a.task_id = b.task_id 
																				INNER JOIN t_student c ON a.stu_id = c.stu_id 
																			WHERE 
																				b.task_id = :taskId 
																				AND 
																				c.stu_id = :studentId ) t
																		SET 
																			t.mark_mode = :markMode ,
																			t.mark_renote = :markNote , 
																			t.mark_resit = :scroll "/>
																			
                                                        <cfset  updateObj = new Query( datasource=application.dnsMaster )/>
														
                                                    	<cfset  updateObj.addParam( name="markMode", value=markMode, cfsqltype="cf_sql_char" ) />
                                                        <cfset  updateObj.addParam( name="markNote", value=markNote, cfsqltype="cf_sql_char" ) />
                                                        <cfset  updateObj.addParam( name="scroll", value=scroll, cfsqltype="cf_sql_decimal", scale=2 )/>
                                                        <cfset  updateObj.addParam( name="taskId", value=taskId, cfsqltype="cf_sql_varchar") />
                                                        <cfset  updateObj.addParam( name="studentId", value=rs["学号"][rs.currentRow], cfsqltype="cf_sql_varchar") />
														
                                                        <cfset  updateObj.execute( sql=sql ) />
                                                        
                                                    </cftransaction>	
												<cfelse>
													<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 未查到成绩提交记录</p>
											</cfif>
									
										<cfelse>
											<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 补考成绩 <cfoutput>#rs["补考成绩"][rs.currentRow]#</cfoutput> 无法识别</p>
									</cfif>
								
								<cfelse>
									<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 未查到成绩提交记录</p>
							</cfif>
							
						</cfloop>

					</cftransaction>

				</cfif>
				
				
			</cfif>
		</cfif> 
		
		<p>导入成绩数据 [<cfoutput>#markUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL( markSubmitEvent, targetArgs )#</cfoutput>">刷新成绩数据</a>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>